package cn.fornever.tools.db.assist.util;

import cn.fornever.tools.db.DBUtil;
import cn.fornever.tools.db.assist.entity.ColumnInfo;
import cn.fornever.tools.db.assist.entity.ForeignKeyInfo;
import cn.fornever.tools.db.assist.entity.PrimaryKeyInfo;
import cn.fornever.tools.db.assist.entity.TableInfo;
import org.apache.commons.lang3.StringUtils;
import org.mybatis.generator.api.IntrospectedColumn;
import org.mybatis.generator.api.JavaTypeResolver;
import org.mybatis.generator.api.dom.java.FullyQualifiedJavaType;
import org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 说明:2017-06-15
 *
 * @author ZhangAY
 */
public class DBServiceCommonImpl implements IDBService {

    private static class SingletonHolder{
        public static DBServiceCommonImpl instance = new DBServiceCommonImpl();
    }
    private DBServiceCommonImpl(){}
    public static DBServiceCommonImpl getInstance(){
        return SingletonHolder.instance;
    }

    protected DatabaseMetaData getDatabaseMetaData(Connection connection){
        return DBUtil.getDatabaseMetaData(connection);
    }

    /**
     * 获取所有表的信息
     * <p>注意,不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.
     *
     * @param connection       数据库连接.
     * @param catalog          数据库的catalog
     * @param schemaPattern    数据库的schema名称.
     * @param tableNamePattern 表名.
     * @param types            表类型.参考取值:"TABLE","VIEW","SYSTEM TABLE","GLOBAL TEMPORARY","LOCAL TEMPORARY","ALIAS", "SYNONYM".允许为 {@code null}
     * @return 符合要求的所有表的信息.如果出现异常,则返回 {@code null}.
     */
    public List<TableInfo> getTableInfo(Connection connection, String catalog, String schemaPattern,
                                               String tableNamePattern, String types[]) {
        List<TableInfo> result = null;
        DatabaseMetaData metaData = getDatabaseMetaData(connection);
        ResultSet tablesResultSet;
        try {
            tablesResultSet = metaData.getTables(catalog, schemaPattern, tableNamePattern, types);
            result = new ArrayList<>();
            while (tablesResultSet.next()) {
                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableCat(tablesResultSet.getString(1));//TABLE_CAT
                tableInfo.setTableSchem(tablesResultSet.getString(2));//TABLE_SCHEM
                tableInfo.setTableName(tablesResultSet.getString(3));//TABLE_NAME
                tableInfo.setTableType(tablesResultSet.getString(4));//TABLE_TYPE
                tableInfo.setRemarks(tablesResultSet.getString(5));//REMARKS
                tableInfo.setTypeCat(tablesResultSet.getString(6));//TYPE_CAT
                tableInfo.setTypeSchem(tablesResultSet.getString(7));//TYPE_SCHEM
                tableInfo.setTypeName(tablesResultSet.getString(8));//TYPE_NAME
                tableInfo.setTypeName(tablesResultSet.getString(9));//SELF_REFERENCING_COL_NAME
                tableInfo.setRefGeneration(tablesResultSet.getString(10));//SELF_REFERENCING_COL_NAME
                result.add(tableInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取所有列的信息.
     * <p>注意,不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.
     *
     * @param connection        数据库连接.
     * @param catalog           数据库的catalog
     * @param schemaPattern     数据库的schema名称.
     * @param tableNamePattern  表名.
     * @param columnNamePattern 列名.
     * @return 符合要求的所有表的信息.如果出现异常,则返回 {@code null}.
     */
    public List<ColumnInfo> getColoumInfo(Connection connection, String catalog, String schemaPattern,
                                                 String tableNamePattern, String columnNamePattern) {
        List<ColumnInfo> result = null;
        DatabaseMetaData metaData = getDatabaseMetaData(connection);
        ResultSet columnResultSet = null;
        ColumnInfo columnInfo;
        Integer maxColumnIndex = 0;//从1开始.通过JDBC能获取到的tablesResultSet中的数据列数.
        try {
            columnResultSet = metaData.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
            result = new ArrayList<>();
            while (columnResultSet.next()) {
                columnInfo = new ColumnInfo();
                result.add(columnInfo);
                columnInfo.setTableCat(columnResultSet.getString(1));//TABLE_CAT
                maxColumnIndex++;
                columnInfo.setTableSchem(columnResultSet.getString(2));//TABLE_SCHEM
                maxColumnIndex++;
                columnInfo.setTableName(columnResultSet.getString(3));//TABLE_NAME
                maxColumnIndex++;
                columnInfo.setColumnName(columnResultSet.getString(4));//COLUMN_NAME
                maxColumnIndex++;
                columnInfo.setDataType(string2Integer(columnResultSet.getString(5), null));//DATA_TYPE
                maxColumnIndex++;
                columnInfo.setTypeName(columnResultSet.getString(6));//TYPE_NAME
                maxColumnIndex++;
                columnInfo.setColumnSize(string2Integer(columnResultSet.getString(7), null));//COLUMN_SIZE
                maxColumnIndex++;
                columnInfo.setBufferLength(string2Integer(columnResultSet.getString(8), null));//BUFFER_LENGTH
                maxColumnIndex++;
                columnInfo.setDecimalDigits(string2Integer(columnResultSet.getString(9), null));//DECIMAL_DIGITS
                maxColumnIndex++;
                columnInfo.setNumPrecRadix(string2Integer(columnResultSet.getString(10), null));//NUM_PREC_RADIX
                maxColumnIndex++;
                columnInfo.setNullable(string2Integer(columnResultSet.getString(11), null));//NULLABLE
                maxColumnIndex++;
                columnInfo.setRemarks(columnResultSet.getString(12));//REMARKS
                maxColumnIndex++;
                columnInfo.setColumnDef(columnResultSet.getString(13));//COLUMN_DEF
                maxColumnIndex++;
                columnInfo.setSqlDataType(string2Integer(columnResultSet.getString(14), null));//SQL_DATA_TYPE
                maxColumnIndex++;
                columnInfo.setSqlDatetimeSub(string2Integer(columnResultSet.getString(15), null));//SQL_DATETIME_SUB
                maxColumnIndex++;
                columnInfo.setCharOctetLength(string2Integer(columnResultSet.getString(16), null));
                maxColumnIndex++;
                columnInfo.setOrdinalPosition(string2Integer(columnResultSet.getString(17), null));
                maxColumnIndex++;
                columnInfo.setIsNullable(columnResultSet.getString(18));
                maxColumnIndex++;
                columnInfo.setScopeCatlog(columnResultSet.getString(19));
                maxColumnIndex++;
                columnInfo.setScopeSchema(columnResultSet.getString(20));
                maxColumnIndex++;
                columnInfo.setScopeTable(columnResultSet.getString(21));
                maxColumnIndex++;
                columnInfo.setSourceDataType(string2Short(columnResultSet.getString(22), null));
                maxColumnIndex++;
                columnInfo.setIsAutoincrement(columnResultSet.getString(23));
                maxColumnIndex++;
                columnInfo.setIsGeneratedcolumn(columnResultSet.getString(24));
                maxColumnIndex++;
                maxColumnIndex = 0;
            }
        } catch (SQLException e) {
            if (columnResultSet != null) {
                try {
                    while (columnResultSet.next()) {
                        columnInfo = new ColumnInfo();
                        result.add(columnInfo);
                        columnInfo.setTableCat(1 <= maxColumnIndex ? columnResultSet.getString(1) : null);//TABLE_CAT
                        columnInfo.setTableSchem(2 <= maxColumnIndex ? columnResultSet.getString(2) : null);//TABLE_SCHEM
                        columnInfo.setTableName(3 <= maxColumnIndex ? columnResultSet.getString(3) : null);//TABLE_NAME
                        columnInfo.setColumnName(4 <= maxColumnIndex ? columnResultSet.getString(4) : null);//COLUMN_NAME
                        columnInfo.setDataType(5 <= maxColumnIndex ? string2Integer(columnResultSet.getString(5), null) : null);//DATA_TYPE
                        columnInfo.setTypeName(6 <= maxColumnIndex ? columnResultSet.getString(6) : null);//TYPE_NAME
                        columnInfo.setColumnSize(7 <= maxColumnIndex ? string2Integer(columnResultSet.getString(7), null) : null);//COLUMN_SIZE
                        columnInfo.setBufferLength(8 <= maxColumnIndex ? string2Integer(columnResultSet.getString(8), null) : null);//BUFFER_LENGTH
                        columnInfo.setDecimalDigits(9 <= maxColumnIndex ? string2Integer(columnResultSet.getString(9), null) : null);//DECIMAL_DIGITS
                        columnInfo.setNumPrecRadix(10 <= maxColumnIndex ? string2Integer(columnResultSet.getString(10), null) : null);//NUM_PREC_RADIX
                        columnInfo.setNullable(11 <= maxColumnIndex ? string2Integer(columnResultSet.getString(11), null) : null);//NULLABLE
                        columnInfo.setRemarks(12 <= maxColumnIndex ? columnResultSet.getString(12) : null);//REMARKS
                        columnInfo.setColumnDef(13 <= maxColumnIndex ? columnResultSet.getString(13) : null);//COLUMN_DEF
                        columnInfo.setSqlDataType(14 <= maxColumnIndex ? string2Integer(columnResultSet.getString(14), null) : null);//SQL_DATA_TYPE
                        columnInfo.setSqlDatetimeSub(15 <= maxColumnIndex ? string2Integer(columnResultSet.getString(15), null) : null);//SQL_DATETIME_SUB
                        columnInfo.setCharOctetLength(16 <= maxColumnIndex ? string2Integer(columnResultSet.getString(16), null) : null);
                        columnInfo.setOrdinalPosition(17 <= maxColumnIndex ? string2Integer(columnResultSet.getString(17), null) : null);
                        columnInfo.setIsNullable(18 <= maxColumnIndex ? columnResultSet.getString(18) : null);
                        columnInfo.setScopeCatlog(19 <= maxColumnIndex ? columnResultSet.getString(19) : null);
                        columnInfo.setScopeSchema(20 <= maxColumnIndex ? columnResultSet.getString(20) : null);
                        columnInfo.setScopeTable(21 <= maxColumnIndex ? columnResultSet.getString(21) : null);
                        columnInfo.setSourceDataType(22 <= maxColumnIndex ? string2Short(columnResultSet.getString(22), null) : null);
                        columnInfo.setIsAutoincrement(23 <= maxColumnIndex ? columnResultSet.getString(23) : null);
                        columnInfo.setIsGeneratedcolumn(24 <= maxColumnIndex ? columnResultSet.getString(24) : null);
                    }
                } catch (SQLException sqlException) {
                    sqlException.printStackTrace();
                }
            }
            e.printStackTrace();
        }

        result = setJavaType(result,connection,catalog, schemaPattern, tableNamePattern);

        return result;
    }

    /**
     * 获取所有表的主键信息
     * <p>注意,不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.
     *
     * @param connection       数据库连接.
     * @param catalog          数据库的catalog
     * @param schemaPattern    数据库的schema名称.
     * @param tableNamePattern 表名.
     * @return 符合要求的所有表的主键信息.如果出现异常, 则返回 {@code null}.
     */
    public List<PrimaryKeyInfo> getPrimaryKeyInfo(Connection connection, String catalog, String schemaPattern,
                                                         String tableNamePattern) {
        List<PrimaryKeyInfo> result = null;
        DatabaseMetaData metaData = getDatabaseMetaData(connection);
        ResultSet tablesResultSet;
        try {
            tablesResultSet = metaData.getPrimaryKeys(catalog, schemaPattern, tableNamePattern);
            result = new ArrayList<>();
            String str;
            while (tablesResultSet.next()) {
                PrimaryKeyInfo primaryKeyInfo = new PrimaryKeyInfo();
                primaryKeyInfo.setTableCat(tablesResultSet.getString(1));//TABLE_CAT
                primaryKeyInfo.setTableSchem(tablesResultSet.getString(2));//TABLE_SCHEM
                primaryKeyInfo.setTableName(tablesResultSet.getString(3));//TABLE_NAME
                primaryKeyInfo.setColumnName(tablesResultSet.getString(4));//COLUMN_NAME
                str = tablesResultSet.getString(5);//KEY_SEQ
                if (str == null) {
                    primaryKeyInfo.setKeySeq(null);
                } else {
                    primaryKeyInfo.setKeySeq(Short.valueOf(str));
                }
                primaryKeyInfo.setPkName(tablesResultSet.getString(6));//PK_NAME
                result.add(primaryKeyInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 获取所有表的外键信息.
     * <p>注意:不同数据库驱动的实现略有不同,不一定支持通配符,也不一定支持null.也不一定是每一个参数都会起作用.比如,MYSQL下 schema 就是无效的.
     *
     * @param connection 数据库连接.
     * @param catalog    数据库的catalog.
     *                   <p>对通配符及null等支持如下:
     *                   <table border='2'>
     *                   <caption>对通配符及null等支持情况</caption>
     *                   <tr>
     *                   <th>数据库驱动</th>
     *                   <th>通配符</th>
     *                   <th>备注</th>
     *                   </tr>
     *                   <tr>
     *                   <th>MYSQL</th>
     *                   <th>支持%和_</th>
     *                   <th>支持null</th>
     *                   </tr>
     *                   <tr>
     *                   <th>ORACLE</th>
     *                   <th>--</th>
     *                   <th>--</th>
     *                   </tr>
     *                   </table>
     * @param schema     数据库的schema名称.
     *                   <p>对通配符及null等支持如下:
     *                   <table border='2'>
     *                   <caption>对通配符及null等支持情况</caption>
     *                   <tr>
     *                   <th>数据库驱动</th>
     *                   <th>通配符</th>
     *                   <th>备注</th>
     *                   </tr>
     *                   <tr>
     *                   <th>MYSQL</th>
     *                   <th>--</th>
     *                   <th>此字段不起作用</th>
     *                   </tr>
     *                   <tr>
     *                   <th>ORACLE</th>
     *                   <th>--</th>
     *                   <th>--</th>
     *                   </tr>
     *                   </table>
     * @param tableName  表名.
     *                   <p>对通配符及null等支持如下:
     *                   <table border='2'>
     *                   <caption>对通配符及null等支持情况</caption>
     *                   <tr>
     *                   <th>数据库驱动</th>
     *                   <th>通配符</th>
     *                   <th>备注</th>
     *                   </tr>
     *                   <tr>
     *                   <th>MYSQL</th>
     *                   <th>不支持</th>
     *                   <th>必须是完整表名,不支持null</th>
     *                   </tr>
     *                   <tr>
     *                   <th>ORACLE</th>
     *                   <th>--</th>
     *                   <th>--</th>
     *                   </tr>
     *                   </table>
     * @return 符合要求的所有表的外键信息.如果出现异常, 则返回 {@code null}.
     */
    public List<ForeignKeyInfo> getForeignKeyInfo(Connection connection, String catalog, String schema, String tableName) {
        List<ForeignKeyInfo> result = null;
        DatabaseMetaData metaData = getDatabaseMetaData(connection);
        ResultSet tablesResultSet;
        try {
            tablesResultSet = metaData.getExportedKeys(catalog, schema, tableName);
            result = new ArrayList<>();
            String str;
            while (tablesResultSet.next()) {
                ForeignKeyInfo foreignKeyInfo = new ForeignKeyInfo();
                foreignKeyInfo.setPktableCat(tablesResultSet.getString(1));//PKTABLE_CAT
                foreignKeyInfo.setPktableSchem(tablesResultSet.getString(2));//PKTABLE_SCHEM
                foreignKeyInfo.setPktableName(tablesResultSet.getString(3));//PKTABLE_NAME
                foreignKeyInfo.setPkcolumnName(tablesResultSet.getString(4));//PKCOLUMN_NAME
                foreignKeyInfo.setFktableCat(tablesResultSet.getString(5));//FKTABLE_CAT
                foreignKeyInfo.setFktableSchem(tablesResultSet.getString(6));//FKTABLE_SCHEM
                foreignKeyInfo.setFktableName(tablesResultSet.getString(7));//FKTABLE_NAME
                foreignKeyInfo.setFkcolumnName(tablesResultSet.getString(8));//FKCOLUMN_NAME
                str = tablesResultSet.getString(9);//KEY_SEQ
                if (str == null) {
                    foreignKeyInfo.setKeySeq(null);
                } else {
                    foreignKeyInfo.setKeySeq(Short.valueOf(str));
                }

                str = tablesResultSet.getString(10);//UPDATE_RULE
                if (str == null) {
                    foreignKeyInfo.setUpdaterule(null);
                } else {
                    foreignKeyInfo.setUpdaterule(Short.valueOf(str));
                }

                str = tablesResultSet.getString(11);//DELETE_RULE
                if (str == null) {
                    foreignKeyInfo.setDeleterule(null);
                } else {
                    foreignKeyInfo.setDeleterule(Short.valueOf(str));
                }

                foreignKeyInfo.setFkName(tablesResultSet.getString(12));//FK_NAME
                foreignKeyInfo.setPkName(tablesResultSet.getString(13));//PK_NAME

                if (str == null) {
                    foreignKeyInfo.setDeferrability(null);
                } else {
                    foreignKeyInfo.setDeferrability(Short.valueOf(str));
                }
                result.add(foreignKeyInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }





}
